package com.youlai.boot.system.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.youlai.boot.system.model.entity.SupplyChainReport;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.youlai.boot.system.model.query.SupplyChainReportQuery;
import com.youlai.boot.system.model.vo.SupplyChainReportVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;
import java.util.Map;

/**
 * 供应链日报Mapper接口
 *
 * @author xiheforever
 * @since 2025-06-04
 */
@Mapper
public interface SupplyChainReportMapper extends BaseMapper<SupplyChainReport> {

    /**
     * 获取供应链日报分页数据
     *
     * @param page 分页对象
     * @param queryParams 查询参数
     * @return {@link Page<SupplyChainReportVO>} 供应链日报分页列表
     */
    Page<SupplyChainReportVO> getSupplyChainReportPage(Page<SupplyChainReportVO> page, SupplyChainReportQuery queryParams);

    /**
     * 获取指定日期的汇总数据
     *
     * @param reportDate 报告日期
     * @return 汇总数据
     */
    @Select("""
            SELECT 
                COUNT(*) as storeCount,
                COALESCE(SUM(spot_amount), 0) as totalSpotAmount,
                COALESCE(SUM(in_transit_amount), 0) as totalInTransitAmount,
                COALESCE(SUM(total_amount), 0) as totalAmount,
                COALESCE(AVG(spot_turnover_days), 0) as avgSpotTurnoverDays,
                COALESCE(AVG(with_in_transit_turnover_days), 0) as avgWithInTransitTurnoverDays,
                COALESCE(SUM(nr_target), 0) as totalNrTarget,
                COALESCE(SUM(ordered_amount), 0) as totalOrderedAmount,
                COALESCE(AVG(achievement_rate), 0) as avgAchievementRate,
                COALESCE(AVG(pv_spot_rate), 0) as avgPvSpotRate
            FROM supply_chain_report 
            WHERE report_date = #{reportDate} AND is_deleted = 0
            """)
    Map<String, Object> getSummaryByDate(@Param("reportDate") LocalDate reportDate);

    /**
     * 获取最近日期的汇总数据
     *
     * @return 最近日期的汇总数据
     */
    @Select("SELECT " +
            "SUM(total_amount) as totalAmount, " +
            "SUM(total_quantity) as totalQuantity, " +
            "SUM(spot_amount) as spotAmount, " +
            "SUM(in_transit_amount) as inTransitAmount, " +
            "AVG(spot_turnover_days) as avgTurnoverDays, " +
            "AVG(pv_spot_rate) as avgPvSpotRate, " +
            "COUNT(*) as storeCount, " +
            "COUNT(CASE WHEN spot_turnover_days > 60 THEN 1 END) as slowTurnoverCount " +
            "FROM supply_chain_report " +
            "WHERE report_date = (SELECT MAX(report_date) FROM supply_chain_report WHERE is_deleted = 0) AND is_deleted = 0")
    Map<String, Object> getLatestSummaryData();

    /**
     * 获取日期范围内按周转天数分类的统计
     *
     * @param startDate 开始日期
     * @param endDate 结束日期
     * @return 周转分析数据
     */
    @Select("""
            SELECT 
                SUM(CASE WHEN spot_turnover_days <= 30 THEN 1 ELSE 0 END) as fastTurnoverCount,
                SUM(CASE WHEN spot_turnover_days > 30 AND spot_turnover_days <= 60 THEN 1 ELSE 0 END) as mediumTurnoverCount,
                SUM(CASE WHEN spot_turnover_days > 60 THEN 1 ELSE 0 END) as slowTurnoverCount,
                (SELECT store_name FROM supply_chain_report 
                 WHERE report_date = (SELECT MAX(report_date) FROM supply_chain_report WHERE is_deleted = 0) AND is_deleted = 0
                 ORDER BY spot_turnover_days ASC LIMIT 1) as bestTurnoverStore,
                (SELECT MIN(spot_turnover_days) FROM supply_chain_report 
                 WHERE report_date = (SELECT MAX(report_date) FROM supply_chain_report WHERE is_deleted = 0) AND is_deleted = 0) as bestTurnoverDays
            FROM supply_chain_report 
            WHERE report_date BETWEEN #{startDate} AND #{endDate} AND is_deleted = 0
            """)
    Map<String, Object> getTurnoverAnalysis(@Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);

    /**
     * 获取最佳周转表现的店铺
     *
     * @param startDate 开始日期
     * @param endDate 结束日期
     * @return 最佳店铺信息
     */
    @Select("SELECT store_name, spot_turnover_days " +
            "FROM supply_chain_report " +
            "WHERE report_date BETWEEN #{startDate} AND #{endDate} AND is_deleted = 0 " +
            "ORDER BY spot_turnover_days ASC " +
            "LIMIT 1")
    Map<String, Object> getBestTurnoverStore(@Param("startDate") LocalDate startDate, 
                                           @Param("endDate") LocalDate endDate);

    /**
     * 获取按日期聚合的趋势数据
     *
     * @param startDate 开始日期
     * @param endDate 结束日期
     * @return 趋势数据列表
     */
    @Select("""
            SELECT 
                report_date,
                COALESCE(AVG(total_amount), 0) as avgTotalAmount,
                COALESCE(AVG(spot_turnover_days), 0) as avgTurnoverDays,
                COALESCE(AVG(achievement_rate), 0) as avgAchievementRate
            FROM supply_chain_report 
            WHERE report_date BETWEEN #{startDate} AND #{endDate} AND is_deleted = 0 
            GROUP BY report_date 
            ORDER BY report_date
            """)
    List<Map<String, Object>> getTrendData(@Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);

    /**
     * 获取需要预警的店铺列表
     *
     * @param reportDate 报告日期
     * @return 预警店铺列表
     */
    @Select("SELECT store_name, spot_turnover_days, pv_spot_rate, achievement_rate, total_amount " +
            "FROM supply_chain_report " +
            "WHERE report_date = #{reportDate} AND is_deleted = 0 " +
            "AND (spot_turnover_days > 30 OR pv_spot_rate < 50 OR achievement_rate < 60)")
    List<Map<String, Object>> getAlertStores(@Param("reportDate") LocalDate reportDate);

    @Select("SELECT MAX(report_date) FROM supply_chain_report WHERE is_deleted = 0")
    LocalDate getLatestReportDate();

}
